import { query } from '../config/database.js'

export const createPlayersTable = async () => {
  const sql = `
    CREATE TABLE IF NOT EXISTS players (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      school_id INT NOT NULL,
      grade_level_id INT NOT NULL,
      gender ENUM('male', 'female') NOT NULL,
      birth_date DATE,
      photo_url VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (school_id) REFERENCES schools(id),
      FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id)
    )
  `
  
  try {
    await query(sql)
    console.log('Players table created or already exists')
  } catch (error) {
    console.error('Error creating players table:', error)
    throw error
  }
}

export const createPlayer = async (playerData) => {
  const { name, school_id, grade_level_id, gender, birth_date, photo_url } = playerData
  const sql = 'INSERT INTO players (name, school_id, grade_level_id, gender, birth_date, photo_url) VALUES (?, ?, ?, ?, ?, ?)'
  try {
    const result = await query(sql, [name, school_id, grade_level_id, gender, birth_date, photo_url])
    return result.insertId
  } catch (error) {
    console.error('Error creating player:', error)
    throw error
  }
}

export const getPlayerById = async (id) => {
  const sql = `
    SELECT p.*, s.name as school_name, gl.name as grade_level_name
    FROM players p
    LEFT JOIN schools s ON p.school_id = s.id
    LEFT JOIN grade_levels gl ON p.grade_level_id = gl.id
    WHERE p.id = ?
  `
  try {
    const results = await query(sql, [id])
    return results[0]
  } catch (error) {
    console.error('Error getting player:', error)
    throw error
  }
}

export const getAllPlayers = async () => {
  const sql = `
    SELECT p.*, s.name as school_name, gl.name as grade_level_name
    FROM players p
    LEFT JOIN schools s ON p.school_id = s.id
    LEFT JOIN grade_levels gl ON p.grade_level_id = gl.id
    ORDER BY p.name
  `
  try {
    return await query(sql)
  } catch (error) {
    console.error('Error getting all players:', error)
    throw error
  }
}

export const updatePlayer = async (id, playerData) => {
  const { name, school_id, grade_level_id, gender, birth_date, photo_url } = playerData
  const sql = `
    UPDATE players 
    SET name = ?, school_id = ?, grade_level_id = ?, gender = ?, birth_date = ?, photo_url = ?
    WHERE id = ?
  `
  try {
    await query(sql, [name, school_id, grade_level_id, gender, birth_date, photo_url, id])
    return true
  } catch (error) {
    console.error('Error updating player:', error)
    throw error
  }
}

export const deletePlayer = async (id) => {
  const sql = 'DELETE FROM players WHERE id = ?'
  try {
    await query(sql, [id])
    return true
  } catch (error) {
    console.error('Error deleting player:', error)
    throw error
  }
} 